import mysql.connector

class DBConn:
	def __init__(self):
		self.cnx = mysql.connector.connect(user='root',
		  								host='localhost',
		                                database='AccessDB')
		self.cursor = self.cnx.cursor()
		print("New connection created!")
		
	def get_access_by_accessid(self, accessId, doorId):
		query = ("SELECT IF(COUNT(*) > 0, 'true', 'false') AS Bool FROM AccessRight a, User u, Door d WHERE a.doorId = " + doorId + " AND u.nfcDetails = '" + accessId + "' AND u.id = a.userId AND d.Id = " + doorId)
		self.cursor.execute(query)
		
		access = str(self.cursor.fetchone()[0])
		
		print access
		
		return access
		
	def get_users(self):	
		try:
			
			query = ("SELECT id, nfcDetails, name FROM User")
			self.cursor.execute(query)

			users_list = []
			for (Id, nfcDetails, Name) in self.cursor:
				u = [Id, nfcDetails, str(Name)]
				users_list.append(u)
			return users_list
		except mysql.connector.Error as err:
			if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
				print("Something is wrong your username or password")
		  	elif err.errno == errorcode.ER_BAD_DB_ERROR:
				print("Database does not exists")
			else:
				print(err)
	def get_doors(self):	
		try:
			
			query = ("SELECT id, doorInfo FROM Door")
			self.cursor.execute(query)

			doors_list = []
			for (Id, doorInfo) in self.cursor:
				d = [Id, str(doorInfo)]
				doors_list.append(d)
			return doors_list
		except mysql.connector.Error as err:
			if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
				print("Something is wrong your username or password")
		  	elif err.errno == errorcode.ER_BAD_DB_ERROR:
				print("Database does not exists")
			else:
				print(err)
	def get_users_and_access(self, id):
		try:
			query = ("SELECT id, name, nfcDetails, (case when " + id + " in (SELECT doorId FROM AccessRight WHERE AccessRight.userId = User.Id) then 'true' else 'false' end) as access FROM User")
			self.cursor.execute(query)
			
			user_access_list = []
			for (Id, Name, AccessId, Access) in self.cursor:
				ua = [Id, str(Name), str(AccessId), str(Access)]
				user_access_list.append(ua)
			return user_access_list
		except mysql.connector.Error as err:
			if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
				print("Something is wrong your username or password")
		  	elif err.errno == errorcode.ER_BAD_DB_ERROR:
				print("Database does not exists")
			else:
				print(err)
	def get_doors_and_access(self, id):
		try:
			query = ("SELECT id, doorInfo, (case when " + id + " in (SELECT userId FROM AccessRight WHERE AccessRight.doorId = Door.Id) then 'true' else 'false' end) as access FROM Door")
			self.cursor.execute(query)
			
			door_access_list = []
			for (Id, Info, Access) in self.cursor:
				da = [Id, str(Info), str(Access)]
				door_access_list.append(da)
			return door_access_list
		except mysql.connector.Error as err:
			if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
				print("Something is wrong your username or password")
		  	elif err.errno == errorcode.ER_BAD_DB_ERROR:
				print("Database does not exists")
			else:
				print(err)
	def set_user_door_access(self, userId, doorId):
		try:
			
			query = ("INSERT INTO AccessRight (userId,doorId) VALUES (" + userId + "," + doorId + ")")
			print(query)
			self.cursor.execute(query)
			self.cnx.commit()
			print("Inserted!")
		except mysql.connector.Error as err:
			if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
				print("Something is wrong your username or password")
		  	elif err.errno == errorcode.ER_BAD_DB_ERROR:
				print("Database does not exists")
			else:
				print(err)
	def remove_user_door_access(self, userId, doorId):
		try: #DELETE INSTEAD!
			query = ("DELETE FROM AccessRight WHERE userId = " + userId + " AND doorId = " + doorId)
			print(query)
			self.cursor.execute(query)
			self.cnx.commit()
			print("Deleted!")
		except mysql.connector.Error as err:
			if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
				print("Something is wrong your username or password")
		  	elif err.errno == errorcode.ER_BAD_DB_ERROR:
				print("Database does not exists")
			else:
				print(err)
	def change_door_info(self, doorId, doorInfo):
		try:
			query = ("Update Door SET doorInfo = '" + doorInfo + "' WHERE id = " + doorId)
			print(query)
			self.cursor.execute(query)
			self.cnx.commit()
			print("Updated!")
		except mysql.connector.Error as err:
			if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
				print("Something is wrong your username or password")
		  	elif err.errno == errorcode.ER_BAD_DB_ERROR:
				print("Database does not exists")
			else:
				print(err)
	def change_user_name(self, userId, name):
		try:
			query = ("Update User SET name = '" + name + "' WHERE id = " + userId)
			print(query)
			self.cursor.execute(query)
			self.cnx.commit()
			print("Updated!")
		except mysql.connector.Error as err:
			if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
				print("Something is wrong your username or password")
		  	elif err.errno == errorcode.ER_BAD_DB_ERROR:
				print("Database does not exists")
			else:
				print(err)
	def change_user_accessid(self, userId, accessId):
		try:
			query = ("Update User SET nfcDetails = '" + accessId + "' WHERE id = " + userId)
			print(query)
			self.cursor.execute(query)
			self.cnx.commit()
			print("Updated!")
		except mysql.connector.Error as err:
			if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
				print("Something is wrong your username or password")
		  	elif err.errno == errorcode.ER_BAD_DB_ERROR:
				print("Database does not exists")
			else:
				print(err)
	def create_user(self, accessId, doors, name):
		try:
			query = ("INSERT INTO User (name, nfcDetails) VALUES ('" + name + "'," + accessId + ")")
			
			print(query)
			self.cursor.execute(query)
			self.cnx.commit()
			print("User Inserted!")
			
			user_id_query = ("SELECT LAST_INSERT_ID()")
			
			self.cursor.execute(user_id_query)
			
			user_id = self.cursor.fetchone()[0];
			
			print(user_id)
			
			for (Door) in doors:
				query2 = ("INSERT INTO AccessRight (userId,doorId) VALUES (" + str(user_id) + "," + str(Door) + ")")
				print(query2)
				self.cursor.execute(query2)
				self.cnx.commit()
			
		except mysql.connector.Error as err:
			if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
				print("Something is wrong your username or password")
		  	elif err.errno == errorcode.ER_BAD_DB_ERROR:
				print("Database does not exists")
			else:
				print(err)
	def create_door(self, info, users):
		try:
			query = ("INSERT INTO Door (doorInfo) VALUES ('" + info + "')")
			
			print(query)
			self.cursor.execute(query)
			self.cnx.commit()
			print("Door Inserted!")
			
			door_id_query = ("SELECT LAST_INSERT_ID()")
			
			self.cursor.execute(door_id_query)
			
			door_id = self.cursor.fetchone()[0];
			
			print(door_id)
			
			for (User) in users:
				query2 = ("INSERT INTO AccessRight (userId,doorId) VALUES (" + str(User) + "," + str(door_id) + ")")
				print(query2)
				self.cursor.execute(query2)
				self.cnx.commit()
			
		except mysql.connector.Error as err:
			if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
				print("Something is wrong your username or password")
		  	elif err.errno == errorcode.ER_BAD_DB_ERROR:
				print("Database does not exists")
			else:
				print(err)
	def create_user(self, name, accessid, doors):
		try:
			query = ("INSERT INTO User (name, nfcDetails) VALUES ('" + name + "','" + accessid + "')")
			
			print(query)
			self.cursor.execute(query)
			self.cnx.commit()
			print("User Inserted!")
			
			user_id_query = ("SELECT LAST_INSERT_ID()")
			
			self.cursor.execute(user_id_query)
			
			user_id = self.cursor.fetchone()[0];
			
			print(user_id)
			
			for (Door) in doors:
				query2 = ("INSERT INTO AccessRight (userId,doorId) VALUES (" + str(user_id) + "," + str(Door) + ")")
				print(query2)
				self.cursor.execute(query2)
				self.cnx.commit()
			
			print("returning true!")
			return True
		except mysql.connector.Error as err:
			if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
				print("Something is wrong your username or password")
		  	elif err.errno == errorcode.ER_BAD_DB_ERROR:
				print("Database does not exists")
			else:
				print(err)
			return False
	def delete_door(self, doorid):
		try:
			query1 = ("DELETE FROM AccessRight WHERE doorId = " + doorid);
			
			self.cursor.execute(query1)
			self.cnx.commit()
			print("Access Right deleted!")
			query2 = ("DELETE FROM Door WHERE id = " + doorid);
			
			self.cursor.execute(query2)
			self.cnx.commit()
			print("Door deleted!")
		except mysql.connector.Error as err:
			if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
				print("Something is wrong your username or password")
		  	elif err.errno == errorcode.ER_BAD_DB_ERROR:
				print("Database does not exists")
			else:
				print(err)
	def delete_user(self, userid):
		try:
			query1 = ("DELETE FROM AccessRight WHERE userId = " + userid);
			
			self.cursor.execute(query1)
			self.cnx.commit()
			print("Access Right deleted!")
			query2 = ("DELETE FROM User WHERE id = " + userid);
			
			self.cursor.execute(query2)
			self.cnx.commit()
			print("User deleted!")
		except mysql.connector.Error as err:
			if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
				print("Something is wrong your username or password")
		  	elif err.errno == errorcode.ER_BAD_DB_ERROR:
				print("Database does not exists")
			else:
				print(err)
	def close(self):
		self.cursor.close()
		self.cnx.close()
		print("Database connection closed!")
